{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Download the datasets\n",
    "In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. Click on the links below to download and save the datasets (.CSV files):\n",
    "1. __CENSUS_DATA:__ https://ibm.box.com/shared/static/05c3415cbfbtfnr2fx4atenb2sd361ze.csv\n",
    "1. __CHICAGO_PUBLIC_SCHOOLS__  https://ibm.box.com/shared/static/f9gjvj1gjmxxzycdhplzt01qtz0s7ew7.csv\n",
    "1. __CHICAGO_CRIME_DATA:__ https://ibm.box.com/shared/static/svflyugsr9zbqy5bmowgswqemfpm1x7f.csv\n",
    "\n",
    "__NOTE:__ Ensure you have downloaded the datasets using the links above instead of directly from the Chicago Data Portal. The versions linked here are subsets of the original datasets and have some of the column names modified to be more database friendly which will make it easier to complete this assignment."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to the database \n",
    "Let us first load the SQL extension and establish a connection with the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: wwg33946@BLUDB'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remember the connection string is of the format:\n",
    "# %sql ibm_db_sa://my-username:my-password@my-hostname:my-port/my-db-name\n",
    "# Enter the connection string for your Db2 on Cloud database instance below\n",
    "%sql ibm_db_sa://wwg33946:lrs%5Ekvlb6m1khxnj@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problems\n",
    "Now write and execute SQL queries to solve assignment problems\n",
    "\n",
    "### Problem 1\n",
    "\n",
    "##### Find the total number of crimes recorded in the CRIME table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wwg33946:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>533</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('533'),)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rows in Crime table\n",
    "%sql SELECT COUNT(*) FROM CHICAGO_CRIME_DATA"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 2\n",
    "\n",
    "##### Retrieve first 10 rows from the CRIME table\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wwg33946:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>case_number</th>\n",
       "        <th>DATE</th>\n",
       "        <th>block</th>\n",
       "        <th>iucr</th>\n",
       "        <th>primary_type</th>\n",
       "        <th>description</th>\n",
       "        <th>location_description</th>\n",
       "        <th>arrest</th>\n",
       "        <th>domestic</th>\n",
       "        <th>beat</th>\n",
       "        <th>district</th>\n",
       "        <th>ward</th>\n",
       "        <th>community_area_number</th>\n",
       "        <th>fbicode</th>\n",
       "        <th>x_coordinate</th>\n",
       "        <th>y_coordinate</th>\n",
       "        <th>YEAR</th>\n",
       "        <th>updatedon</th>\n",
       "        <th>latitude</th>\n",
       "        <th>longitude</th>\n",
       "        <th>location</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3512276</td>\n",
       "        <td>HK587712</td>\n",
       "        <td>08/28/2004 05:50:56 PM</td>\n",
       "        <td>047XX S KEDZIE AVE</td>\n",
       "        <td>890</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>FROM BUILDING</td>\n",
       "        <td>SMALL RETAIL STORE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>911</td>\n",
       "        <td>9</td>\n",
       "        <td>14</td>\n",
       "        <td>58</td>\n",
       "        <td>6</td>\n",
       "        <td>1155838</td>\n",
       "        <td>1873050</td>\n",
       "        <td>2004</td>\n",
       "        <td>02/10/2018 03:50:01 PM</td>\n",
       "        <td>41.80744050</td>\n",
       "        <td>-87.70395585</td>\n",
       "        <td>(41.8074405, -87.703955849)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3406613</td>\n",
       "        <td>HK456306</td>\n",
       "        <td>06/26/2004 12:40:00 PM</td>\n",
       "        <td>009XX N CENTRAL PARK AVE</td>\n",
       "        <td>820</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>$500 AND UNDER</td>\n",
       "        <td>OTHER</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>1112</td>\n",
       "        <td>11</td>\n",
       "        <td>27</td>\n",
       "        <td>23</td>\n",
       "        <td>6</td>\n",
       "        <td>1152206</td>\n",
       "        <td>1906127</td>\n",
       "        <td>2004</td>\n",
       "        <td>02/28/2018 03:56:25 PM</td>\n",
       "        <td>41.89827996</td>\n",
       "        <td>-87.71640551</td>\n",
       "        <td>(41.898279962, -87.716405505)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8002131</td>\n",
       "        <td>HT233595</td>\n",
       "        <td>04/04/2011 05:45:00 AM</td>\n",
       "        <td>043XX S WABASH AVE</td>\n",
       "        <td>820</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>$500 AND UNDER</td>\n",
       "        <td>NURSING HOME/RETIREMENT HOME</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>221</td>\n",
       "        <td>2</td>\n",
       "        <td>3</td>\n",
       "        <td>38</td>\n",
       "        <td>6</td>\n",
       "        <td>1177436</td>\n",
       "        <td>1876313</td>\n",
       "        <td>2011</td>\n",
       "        <td>02/10/2018 03:50:01 PM</td>\n",
       "        <td>41.81593313</td>\n",
       "        <td>-87.62464213</td>\n",
       "        <td>(41.815933131, -87.624642127)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7903289</td>\n",
       "        <td>HT133522</td>\n",
       "        <td>12/30/2010 04:30:00 PM</td>\n",
       "        <td>083XX S KINGSTON AVE</td>\n",
       "        <td>840</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>FINANCIAL ID THEFT: OVER $300</td>\n",
       "        <td>RESIDENCE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>423</td>\n",
       "        <td>4</td>\n",
       "        <td>7</td>\n",
       "        <td>46</td>\n",
       "        <td>6</td>\n",
       "        <td>1194622</td>\n",
       "        <td>1850125</td>\n",
       "        <td>2010</td>\n",
       "        <td>02/10/2018 03:50:01 PM</td>\n",
       "        <td>41.74366532</td>\n",
       "        <td>-87.56246276</td>\n",
       "        <td>(41.743665322, -87.562462756)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10402076</td>\n",
       "        <td>HZ138551</td>\n",
       "        <td>02/02/2016 07:30:00 PM</td>\n",
       "        <td>033XX W 66TH ST</td>\n",
       "        <td>820</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>$500 AND UNDER</td>\n",
       "        <td>ALLEY</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>831</td>\n",
       "        <td>8</td>\n",
       "        <td>15</td>\n",
       "        <td>66</td>\n",
       "        <td>6</td>\n",
       "        <td>1155240</td>\n",
       "        <td>1860661</td>\n",
       "        <td>2016</td>\n",
       "        <td>02/10/2018 03:50:01 PM</td>\n",
       "        <td>41.77345530</td>\n",
       "        <td>-87.70648047</td>\n",
       "        <td>(41.773455295, -87.706480471)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7732712</td>\n",
       "        <td>HS540106</td>\n",
       "        <td>09/29/2010 07:59:00 AM</td>\n",
       "        <td>006XX W CHICAGO AVE</td>\n",
       "        <td>810</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>OVER $500</td>\n",
       "        <td>PARKING LOT/GARAGE(NON.RESID.)</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>1323</td>\n",
       "        <td>12</td>\n",
       "        <td>27</td>\n",
       "        <td>24</td>\n",
       "        <td>6</td>\n",
       "        <td>1171668</td>\n",
       "        <td>1905607</td>\n",
       "        <td>2010</td>\n",
       "        <td>02/10/2018 03:50:01 PM</td>\n",
       "        <td>41.89644677</td>\n",
       "        <td>-87.64493868</td>\n",
       "        <td>(41.896446772, -87.644938678)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10769475</td>\n",
       "        <td>HZ534771</td>\n",
       "        <td>11/30/2016 01:15:00 AM</td>\n",
       "        <td>050XX N KEDZIE AVE</td>\n",
       "        <td>810</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>OVER $500</td>\n",
       "        <td>STREET</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>1713</td>\n",
       "        <td>17</td>\n",
       "        <td>33</td>\n",
       "        <td>14</td>\n",
       "        <td>6</td>\n",
       "        <td>1154133</td>\n",
       "        <td>1933314</td>\n",
       "        <td>2016</td>\n",
       "        <td>02/10/2018 03:50:01 PM</td>\n",
       "        <td>41.97284491</td>\n",
       "        <td>-87.70860008</td>\n",
       "        <td>(41.972844913, -87.708600079)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4494340</td>\n",
       "        <td>HL793243</td>\n",
       "        <td>12/16/2005 04:45:00 PM</td>\n",
       "        <td>005XX E PERSHING RD</td>\n",
       "        <td>860</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>RETAIL THEFT</td>\n",
       "        <td>GROCERY FOOD STORE</td>\n",
       "        <td>TRUE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>213</td>\n",
       "        <td>2</td>\n",
       "        <td>3</td>\n",
       "        <td>38</td>\n",
       "        <td>6</td>\n",
       "        <td>1180448</td>\n",
       "        <td>1879234</td>\n",
       "        <td>2005</td>\n",
       "        <td>02/28/2018 03:56:25 PM</td>\n",
       "        <td>41.82387989</td>\n",
       "        <td>-87.61350386</td>\n",
       "        <td>(41.823879885, -87.613503857)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3778925</td>\n",
       "        <td>HL149610</td>\n",
       "        <td>01/28/2005 05:00:00 PM</td>\n",
       "        <td>100XX S WASHTENAW AVE</td>\n",
       "        <td>810</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>OVER $500</td>\n",
       "        <td>STREET</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>2211</td>\n",
       "        <td>22</td>\n",
       "        <td>19</td>\n",
       "        <td>72</td>\n",
       "        <td>6</td>\n",
       "        <td>1160129</td>\n",
       "        <td>1838040</td>\n",
       "        <td>2005</td>\n",
       "        <td>02/28/2018 03:56:25 PM</td>\n",
       "        <td>41.71128051</td>\n",
       "        <td>-87.68917910</td>\n",
       "        <td>(41.711280513, -87.689179097)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3324217</td>\n",
       "        <td>HK361551</td>\n",
       "        <td>05/13/2004 02:15:00 PM</td>\n",
       "        <td>033XX W BELMONT AVE</td>\n",
       "        <td>820</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>$500 AND UNDER</td>\n",
       "        <td>SMALL RETAIL STORE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>1733</td>\n",
       "        <td>17</td>\n",
       "        <td>35</td>\n",
       "        <td>21</td>\n",
       "        <td>6</td>\n",
       "        <td>1153590</td>\n",
       "        <td>1921084</td>\n",
       "        <td>2004</td>\n",
       "        <td>02/28/2018 03:56:25 PM</td>\n",
       "        <td>41.93929582</td>\n",
       "        <td>-87.71092344</td>\n",
       "        <td>(41.939295821, -87.710923442)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3512276, 'HK587712', '08/28/2004 05:50:56 PM', '047XX S KEDZIE AVE', '890', 'THEFT', 'FROM BUILDING', 'SMALL RETAIL STORE', 'FALSE', 'FALSE', 911, 9, 14, 58, '6', 1155838, 1873050, 2004, '02/10/2018 03:50:01 PM', Decimal('41.80744050'), Decimal('-87.70395585'), '(41.8074405, -87.703955849)'),\n",
       " (3406613, 'HK456306', '06/26/2004 12:40:00 PM', '009XX N CENTRAL PARK AVE', '820', 'THEFT', '$500 AND UNDER', 'OTHER', 'FALSE', 'FALSE', 1112, 11, 27, 23, '6', 1152206, 1906127, 2004, '02/28/2018 03:56:25 PM', Decimal('41.89827996'), Decimal('-87.71640551'), '(41.898279962, -87.716405505)'),\n",
       " (8002131, 'HT233595', '04/04/2011 05:45:00 AM', '043XX S WABASH AVE', '820', 'THEFT', '$500 AND UNDER', 'NURSING HOME/RETIREMENT HOME', 'FALSE', 'FALSE', 221, 2, 3, 38, '6', 1177436, 1876313, 2011, '02/10/2018 03:50:01 PM', Decimal('41.81593313'), Decimal('-87.62464213'), '(41.815933131, -87.624642127)'),\n",
       " (7903289, 'HT133522', '12/30/2010 04:30:00 PM', '083XX S KINGSTON AVE', '840', 'THEFT', 'FINANCIAL ID THEFT: OVER $300', 'RESIDENCE', 'FALSE', 'FALSE', 423, 4, 7, 46, '6', 1194622, 1850125, 2010, '02/10/2018 03:50:01 PM', Decimal('41.74366532'), Decimal('-87.56246276'), '(41.743665322, -87.562462756)'),\n",
       " (10402076, 'HZ138551', '02/02/2016 07:30:00 PM', '033XX W 66TH ST', '820', 'THEFT', '$500 AND UNDER', 'ALLEY', 'FALSE', 'FALSE', 831, 8, 15, 66, '6', 1155240, 1860661, 2016, '02/10/2018 03:50:01 PM', Decimal('41.77345530'), Decimal('-87.70648047'), '(41.773455295, -87.706480471)'),\n",
       " (7732712, 'HS540106', '09/29/2010 07:59:00 AM', '006XX W CHICAGO AVE', '810', 'THEFT', 'OVER $500', 'PARKING LOT/GARAGE(NON.RESID.)', 'FALSE', 'FALSE', 1323, 12, 27, 24, '6', 1171668, 1905607, 2010, '02/10/2018 03:50:01 PM', Decimal('41.89644677'), Decimal('-87.64493868'), '(41.896446772, -87.644938678)'),\n",
       " (10769475, 'HZ534771', '11/30/2016 01:15:00 AM', '050XX N KEDZIE AVE', '810', 'THEFT', 'OVER $500', 'STREET', 'FALSE', 'FALSE', 1713, 17, 33, 14, '6', 1154133, 1933314, 2016, '02/10/2018 03:50:01 PM', Decimal('41.97284491'), Decimal('-87.70860008'), '(41.972844913, -87.708600079)'),\n",
       " (4494340, 'HL793243', '12/16/2005 04:45:00 PM', '005XX E PERSHING RD', '860', 'THEFT', 'RETAIL THEFT', 'GROCERY FOOD STORE', 'TRUE', 'FALSE', 213, 2, 3, 38, '6', 1180448, 1879234, 2005, '02/28/2018 03:56:25 PM', Decimal('41.82387989'), Decimal('-87.61350386'), '(41.823879885, -87.613503857)'),\n",
       " (3778925, 'HL149610', '01/28/2005 05:00:00 PM', '100XX S WASHTENAW AVE', '810', 'THEFT', 'OVER $500', 'STREET', 'FALSE', 'FALSE', 2211, 22, 19, 72, '6', 1160129, 1838040, 2005, '02/28/2018 03:56:25 PM', Decimal('41.71128051'), Decimal('-87.68917910'), '(41.711280513, -87.689179097)'),\n",
       " (3324217, 'HK361551', '05/13/2004 02:15:00 PM', '033XX W BELMONT AVE', '820', 'THEFT', '$500 AND UNDER', 'SMALL RETAIL STORE', 'FALSE', 'FALSE', 1733, 17, 35, 21, '6', 1153590, 1921084, 2004, '02/28/2018 03:56:25 PM', Decimal('41.93929582'), Decimal('-87.71092344'), '(41.939295821, -87.710923442)')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM CHICAGO_CRIME_DATA LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 3\n",
    "\n",
    "##### How many crimes involve an arrest?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wwg33946:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>163</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('163'),)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(*) FROM CHICAGO_CRIME_DATA WHERE arrest = 'TRUE'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 4\n",
    "\n",
    "##### Which unique types of crimes have been recorded at GAS STATION locations?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wwg33946:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>primary_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CRIMINAL TRESPA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NARCOTICS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ROBBERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>THEFT</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('CRIMINAL TRESPA',), ('NARCOTICS',), ('ROBBERY',), ('THEFT',)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT(primary_type) FROM CHICAGO_CRIME_DATA WHERE location_description = 'GAS STATION'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hint: Which column lists types of crimes e.g. THEFT?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 5\n",
    "\n",
    "##### In the CENUS_DATA table list all Community Areas whose names start with the letter ‘B’."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wwg33946:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belmont Cragin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Burnside</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brighton Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bridgeport</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Beverly</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Belmont Cragin',),\n",
       " ('Burnside',),\n",
       " ('Brighton Park',),\n",
       " ('Bridgeport',),\n",
       " ('Beverly',)]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT community_area_name FROM CENSUS_DATA WHERE community_area_name LIKE 'B%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 6\n",
    "\n",
    "##### Which schools in Community Areas 10 to 15 are healthy school certified?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wwg33946:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>School_ID</th>\n",
       "        <th>name_of_school</th>\n",
       "        <th>Elementary, Middle, or High School</th>\n",
       "        <th>Street_Address</th>\n",
       "        <th>City</th>\n",
       "        <th>State</th>\n",
       "        <th>ZIP_Code</th>\n",
       "        <th>Phone_Number</th>\n",
       "        <th>Link</th>\n",
       "        <th>Network_Manager</th>\n",
       "        <th>Collaborative_Name</th>\n",
       "        <th>Adequate_Yearly_Progress_Made_</th>\n",
       "        <th>Track_Schedule</th>\n",
       "        <th>CPS_Performance_Policy_Status</th>\n",
       "        <th>CPS_Performance_Policy_Level</th>\n",
       "        <th>healthy_school_certified</th>\n",
       "        <th>Safety_Icon</th>\n",
       "        <th>safety_score</th>\n",
       "        <th>Family_Involvement_Icon</th>\n",
       "        <th>Family_Involvement_Score</th>\n",
       "        <th>Environment_Icon</th>\n",
       "        <th>Environment_Score</th>\n",
       "        <th>Instruction_Icon</th>\n",
       "        <th>Instruction_Score</th>\n",
       "        <th>Leaders_Icon</th>\n",
       "        <th>Leaders_Score</th>\n",
       "        <th>Teachers_Icon</th>\n",
       "        <th>Teachers_Score</th>\n",
       "        <th>Parent_Engagement_Icon</th>\n",
       "        <th>Parent_Engagement_Score</th>\n",
       "        <th>Parent_Environment_Icon</th>\n",
       "        <th>Parent_Environment_Score</th>\n",
       "        <th>average_student_attendance</th>\n",
       "        <th>Rate_of_Misconducts__per_100_students_</th>\n",
       "        <th>Average_Teacher_Attendance</th>\n",
       "        <th>Individualized_Education_Program_Compliance_Rate</th>\n",
       "        <th>Pk_2_Literacy__</th>\n",
       "        <th>Pk_2_Math__</th>\n",
       "        <th>Gr3_5_Grade_Level_Math__</th>\n",
       "        <th>Gr3_5_Grade_Level_Read__</th>\n",
       "        <th>Gr3_5_Keep_Pace_Read__</th>\n",
       "        <th>Gr3_5_Keep_Pace_Math__</th>\n",
       "        <th>Gr6_8_Grade_Level_Math__</th>\n",
       "        <th>Gr6_8_Grade_Level_Read__</th>\n",
       "        <th>Gr6_8_Keep_Pace_Math_</th>\n",
       "        <th>Gr6_8_Keep_Pace_Read__</th>\n",
       "        <th>Gr_8_Explore_Math__</th>\n",
       "        <th>Gr_8_Explore_Read__</th>\n",
       "        <th>ISAT_Exceeding_Math__</th>\n",
       "        <th>ISAT_Exceeding_Reading__</th>\n",
       "        <th>ISAT_Value_Add_Math</th>\n",
       "        <th>ISAT_Value_Add_Read</th>\n",
       "        <th>ISAT_Value_Add_Color_Math</th>\n",
       "        <th>ISAT_Value_Add_Color_Read</th>\n",
       "        <th>Students_Taking__Algebra__</th>\n",
       "        <th>Students_Passing__Algebra__</th>\n",
       "        <th>9th Grade EXPLORE (2009)</th>\n",
       "        <th>9th Grade EXPLORE (2010)</th>\n",
       "        <th>10th Grade PLAN (2009)</th>\n",
       "        <th>10th Grade PLAN (2010)</th>\n",
       "        <th>Net_Change_EXPLORE_and_PLAN</th>\n",
       "        <th>11th Grade Average ACT (2011)</th>\n",
       "        <th>Net_Change_PLAN_and_ACT</th>\n",
       "        <th>College_Eligibility__</th>\n",
       "        <th>Graduation_Rate__</th>\n",
       "        <th>College_Enrollment_Rate__</th>\n",
       "        <th>college_enrollment</th>\n",
       "        <th>General_Services_Route</th>\n",
       "        <th>Freshman_on_Track_Rate__</th>\n",
       "        <th>x_coordinate</th>\n",
       "        <th>y_coordinate</th>\n",
       "        <th>Latitude</th>\n",
       "        <th>Longitude</th>\n",
       "        <th>community_area_number</th>\n",
       "        <th>community_area_name</th>\n",
       "        <th>Ward</th>\n",
       "        <th>Police_District</th>\n",
       "        <th>Location</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>609995</td>\n",
       "        <td>Rufus M Hitch Elementary School</td>\n",
       "        <td>ES</td>\n",
       "        <td>5625 N McVicker Ave</td>\n",
       "        <td>Chicago</td>\n",
       "        <td>IL</td>\n",
       "        <td>60646</td>\n",
       "        <td>(773) 534-1189</td>\n",
       "        <td>http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_609995.pdf</td>\n",
       "        <td>O&#x27;Hare Elementary Network</td>\n",
       "        <td>NORTH-NORTHWEST SIDE COLLABORATIVE</td>\n",
       "        <td>No</td>\n",
       "        <td>Standard</td>\n",
       "        <td>Not on Probation</td>\n",
       "        <td>Level 1</td>\n",
       "        <td>Yes</td>\n",
       "        <td>Strong</td>\n",
       "        <td>64</td>\n",
       "        <td>Strong</td>\n",
       "        <td>77</td>\n",
       "        <td>Average</td>\n",
       "        <td>55</td>\n",
       "        <td>Average</td>\n",
       "        <td>58</td>\n",
       "        <td>Strong</td>\n",
       "        <td>65</td>\n",
       "        <td>Strong</td>\n",
       "        <td>70</td>\n",
       "        <td>Strong</td>\n",
       "        <td>55</td>\n",
       "        <td>Strong</td>\n",
       "        <td>54</td>\n",
       "        <td>95.10%</td>\n",
       "        <td>3.7</td>\n",
       "        <td>96.60%</td>\n",
       "        <td>98.60%</td>\n",
       "        <td>73.7</td>\n",
       "        <td>27.6</td>\n",
       "        <td>62.2</td>\n",
       "        <td>51.7</td>\n",
       "        <td>50</td>\n",
       "        <td>67.8</td>\n",
       "        <td>56.5</td>\n",
       "        <td>54.8</td>\n",
       "        <td>52</td>\n",
       "        <td>63.3</td>\n",
       "        <td>22.9</td>\n",
       "        <td>37.5</td>\n",
       "        <td>29.5</td>\n",
       "        <td>24.7</td>\n",
       "        <td>-0.1</td>\n",
       "        <td>0.0</td>\n",
       "        <td>Yellow</td>\n",
       "        <td>Yellow</td>\n",
       "        <td>NDA</td>\n",
       "        <td>NDA</td>\n",
       "        <td>NDA</td>\n",
       "        <td>NDA</td>\n",
       "        <td>NDA</td>\n",
       "        <td>NDA</td>\n",
       "        <td>NDA</td>\n",
       "        <td>NDA</td>\n",
       "        <td>NDA</td>\n",
       "        <td>NDA</td>\n",
       "        <td>NDA</td>\n",
       "        <td>NDA</td>\n",
       "        <td>542</td>\n",
       "        <td>30</td>\n",
       "        <td>NDA</td>\n",
       "        <td>1134940.195</td>\n",
       "        <td>1937129.571</td>\n",
       "        <td>41.98367756</td>\n",
       "        <td>-87.77908614</td>\n",
       "        <td>10</td>\n",
       "        <td>NORWOOD PARK</td>\n",
       "        <td>45</td>\n",
       "        <td>16</td>\n",
       "        <td>(41.98367756, -87.77908614)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(609995, 'Rufus M Hitch Elementary School', 'ES', '5625 N McVicker Ave', 'Chicago', 'IL', 60646, '(773) 534-1189', 'http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_609995.pdf', \"O'Hare Elementary Network\", 'NORTH-NORTHWEST SIDE COLLABORATIVE', 'No', 'Standard', 'Not on Probation', 'Level 1', 'Yes', 'Strong', 64, 'Strong', '77', 'Average', 55, 'Average', 58, 'Strong', '65', 'Strong', '70', 'Strong', '55', 'Strong', '54', '95.10%', Decimal('3.7'), '96.60%', '98.60%', '73.7', '27.6', '62.2', '51.7', '50', '67.8', '56.5', '54.8', '52', '63.3', '22.9', '37.5', Decimal('29.5'), Decimal('24.7'), Decimal('-0.1'), Decimal('0.0'), 'Yellow', 'Yellow', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 542, 30, 'NDA', Decimal('1134940.195'), Decimal('1937129.571'), Decimal('41.98367756'), Decimal('-87.77908614'), 10, 'NORWOOD PARK', 45, 16, '(41.98367756, -87.77908614)')]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM CHICAGO_PUBLIC_SCHOOLS \n",
    "WHERE (community_area_number BETWEEN 10 AND 15) AND healthy_school_certified = 'Yes' "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 7\n",
    "\n",
    "##### What is the average school Safety Score? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wwg33946:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49.504873</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('49.504873'),)]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT AVG(safety_score) FROM CHICAGO_PUBLIC_SCHOOLS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 8\n",
    "\n",
    "##### List the top 5 Community Areas by average College Enrollment [number of students] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wwg33946:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LINCOLN PARK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARMOUR SQUARE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NEW CITY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ASHBURN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOUTH SHORE</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('LINCOLN PARK',),\n",
       " ('ARMOUR SQUARE',),\n",
       " ('NEW CITY',),\n",
       " ('ASHBURN',),\n",
       " ('SOUTH SHORE',)]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT community_area_name FROM CHICAGO_PUBLIC_SCHOOLS \n",
    "ORDER BY (SELECT AVG(college_enrollment) FROM CHICAGO_PUBLIC_SCHOOLS) DESC LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 9\n",
    "\n",
    "##### Use a sub-query to determine which Community Area has the least value for school Safety Score? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wwg33946:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WASHINGTON PARK</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('WASHINGTON PARK',)]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT community_area_name FROM CHICAGO_PUBLIC_SCHOOLS \n",
    "WHERE safety_score = (SELECT MIN(safety_score) FROM CHICAGO_PUBLIC_SCHOOLS)               "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 10\n",
    "\n",
    "##### [Without using an explicit JOIN operator] Find the Per Capita Income of the Community Area which has a school Safety Score of 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wwg33946:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>per_capita_income</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13785</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(13785,)]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT community_area_name FROM CHICAGO_PUBLIC_SCHOOLS WHERE safety_score = 1;\n",
    "SELECT per_capita_income FROM CENSUS_DATA WHERE community_area_name = 'Washington Park'"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  },
  "widgets": {
   "state": {},
   "version": "1.1.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
